﻿Imports MySql.Data.MySqlClient
Imports System.Xml
Imports System.Security.Cryptography
Imports System.Security.Cryptography.Xml

Module DatabaseMySQL
    Public dbHost As String = "localhost"
    Public dbPort As Integer = 3306
    Public dbUser As String = "root"
    Public dbPass As String = "8444579"
    Public dbName As String = "db_mserver"

    Function mysql_non_query(ByVal command_text As String, Optional ByVal is_local As Boolean = True, Optional ByVal host As String = "",
                               Optional ByVal port As Integer = 3306, Optional ByVal username As String = "", Optional ByVal password As String = "",
                               Optional ByVal database As String = "")
        Dim connection As MySqlConnection
        Dim command As MySqlCommand

        If is_local = True Then
            host = dbHost
            port = dbPort
            username = dbUser
            password = dbPass
            database = dbName
        End If

        Dim connection_string As String = String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4}", host, port, username, password, database)

        connection = New MySqlConnection(connection_string)
        Try
            connection.Open()

            command = connection.CreateCommand()
            command.CommandText = command_text
            command.ExecuteNonQuery()
            command.Dispose()

            connection.Close()
            connection.Dispose()
        Catch ex As Exception
            Return False
        End Try

        Return True
    End Function

    Function mysql_execute_script(ByVal query As String, Optional ByVal is_local As Boolean = True, Optional ByVal host As String = "",
                               Optional ByVal port As Integer = 3306, Optional ByVal username As String = "", Optional ByVal password As String = "",
                               Optional ByVal database As String = "")
        Dim connection As MySqlConnection
        Dim script As MySqlScript

        If is_local = True Then
            host = dbHost
            port = dbPort
            username = dbUser
            password = dbPass
            database = dbName
        End If

        Dim connection_string As String = String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4}", host, port, username, password, database)

        connection = New MySqlConnection(connection_string)
        Try
            connection.Open()
            script = New MySqlScript(connection, query)
            script.Delimiter = "$$"
            script.Execute()

            connection.Close()
            connection.Dispose()
        Catch ex As Exception
            Return False
        End Try

        Return True
    End Function

    Function mysql_query(ByVal command_text As String, Optional ByVal col_index As Integer = -1, Optional ByVal is_local As Boolean = True, Optional ByVal host As String = "",
                               Optional ByVal port As Integer = 3306, Optional ByVal username As String = "", Optional ByVal password As String = "",
                               Optional ByVal database As String = "")
        If is_local = True Then
            host = dbHost
            port = dbPort
            username = dbUser
            password = dbPass
            database = dbName
        End If

        Dim connection_string As String = String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4};AllowZeroDateTime=true", host, port, username, password, database)
        Dim connection As New MySqlConnection(connection_string)

        Try
            connection.Open()
            If col_index < 0 Then 'return data table
                Dim data As New DataTable
                Dim adapter As New MySqlDataAdapter(command_text, connection)
                adapter.Fill(data)
                adapter.Dispose()
                data.Dispose()
                connection.Close()
                connection.Dispose()

                Return data
            Else 'return reader
                Dim command As MySqlCommand = connection.CreateCommand()
                Dim reader As MySqlDataReader
                Dim result As String
                command.CommandText = command_text
                reader = command.ExecuteReader()
                reader.Read()
                result = reader.GetValue(col_index).ToString
                command.Dispose()
                reader.Close()
                connection.Close()
                connection.Dispose()

                Return result
            End If
        Catch ex As Exception
            Return False
        End Try
    End Function

    Function mysql_show_databases(Optional ByVal is_local As Boolean = True, Optional ByVal host As String = "",
                             Optional ByVal port As Integer = 3306, Optional ByVal username As String = "", Optional ByVal password As String = "")
        If is_local = True Then
            host = dbHost
            port = dbPort
            username = dbUser
            password = dbPass
        End If

        Dim connection_string As String = String.Format("Server={0};Port={1};User Id={2};Password={3}", host, port, username, password)

        Dim connection As New MySqlConnection(connection_string)
        Dim data As New DataTable
        Try
            connection.Open()

            Dim adapter As New MySqlDataAdapter("SHOW DATABASES", connection)
            adapter.Fill(data)
            adapter.Dispose()
            data.Dispose()

            connection.Close()
            connection.Dispose()
        Catch ex As Exception
            MsgBox("Can't Connect To Database Server" & vbCrLf & "Response: " & ex.Message.ToString)
            Return False
        End Try

        Return data
    End Function

    Function mysql_isconnected(Optional ByVal is_local As Boolean = True, Optional ByVal host As String = "", Optional ByVal port As Integer = 3306,
                              Optional ByVal username As String = "", Optional ByVal password As String = "",
                              Optional ByVal database As String = "")
        If is_local = True Then
            host = dbHost
            port = dbPort
            username = dbUser
            password = dbPass
            database = dbName
        Else
            If database.Equals("") Then database = "information_schema"
        End If

        Dim connection_string As String = String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4}", host, port, username, password, database)

        Dim connection As New MySqlConnection(connection_string)
        Try
            connection.Open()
            connection.Close()
            connection.Dispose()
        Catch ex As Exception
            Return False
            Exit Function
        End Try

        Return True
    End Function

End Module
